﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Threading;
using System.Xml;
using System.Xml.Serialization;
using System.Windows.Forms;
using System.Threading;
using FilesMerger2._0.Properties;

namespace MergeAndExecute._0
{
    class SQLExecutor
    {
        public static String ExecuteScalar(String queryName, String query)
        {
            //Thread.Sleep(5000);
            query = query.Trim();
            query = query.Trim(new[] {';', '\n', '\r', '\t', ' '});
            query = query.Trim();
            var result = new StringBuilder();
            try
            {
                String connectionString = Settings.Default.ConnectionStr;
                using (var cn = new OdbcConnection(connectionString))
                {

                    var cmd = new OdbcCommand(query, cn);
                    cn.ConnectionTimeout = 600;
                    cmd.Connection = cn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 0;

                    OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
                    cn.Open();
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    result.Append(TableToString(table));
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
               // MessageBox.Show(String.Format("An error occured while executing {0}. {1}", queryName, ex.ToString()));
                result.Append("Error: " + ex.Message);
                result.AppendLine();
            }
           // MessageBox.Show(result.ToString());
            return result.ToString();
        }

        private static String TableToString(DataTable table)
        {
            StringBuilder result = new StringBuilder();
            Int32 i = 0;
            foreach (DataRow row in table.Rows)
            {
                Int32 j = 0;
                foreach (DataColumn column in table.Columns)
                {
                    if (i == 2 && j == 0)
                    {
                        String ss = Convert.ToString(row[column.ColumnName]);
                        String st = row[column.ColumnName].GetType().ToString();
                        System.IO.MemoryStream _MemoryStream = new System.IO.MemoryStream();
                        System.Runtime.Serialization.Formatters.Binary.BinaryFormatter _BinaryFormatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
                        _BinaryFormatter.Serialize(_MemoryStream, row[column.ColumnName]);
                        byte[] bytes = _MemoryStream.ToArray();
                        String se = Encoding.GetEncoding(1251).GetString(bytes);
                        String sb = String.Empty;
                        foreach (byte b in bytes)
                        {
                            sb += b.ToString("x");
                        }
                       // MessageBox.Show("sb:"+sb+" enc:"+Encoding.GetEncoding(1251).GetString(bytes));
                    }
                    result.Append(Convert.ToString(row[column.ColumnName]));
                    result.Append("\t");
                    j++;
                }
                result.Append(Environment.NewLine);
                i++;
            }
            return result.ToString();
        }

        public static DataTable Execute(String query)
        {
            query = query.Trim();
            query = query.Trim(new[] { ';', '\n', '\r', '\t', ' ' });
            query = query.Trim();
            DataTable result = null;
            try
            {
                String connectionString = Settings.Default.ConnectionStr;
                using (var cn = new OdbcConnection(connectionString))
                {

                    var cmd = new OdbcCommand(query, cn);
                    cn.ConnectionTimeout = 600;
                    cmd.Connection = cn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 0;

                    var adapter = new OdbcDataAdapter(cmd);
                    cn.Open();
                    result = new DataTable();
                    adapter.Fill(result);

                    cn.Close();
                }
                //using (var cn = new SqlConnection(connectionString))
                //{
                //    var cmd = new SqlCommand(query);

                //    cmd.Connection = cn;
                //    cmd.CommandType = CommandType.Text;
                //    cmd.CommandTimeout = 0;

                //    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //    cn.Open();
                //    result = new DataTable();
                //    //adapter.FillSchema(result, SchemaType.Source);
                //    adapter.Fill(result);


                //    cn.Close();
                //}
            }
            catch (Exception ex)
            {
                // MessageBox.Show(String.Format("An error occured while executing {0}. {1}", queryName, ex.ToString()));
            }
            // MessageBox.Show(result.ToString());
            return result;
        }
    }
}
